package com.export.utils;

import com.export.resolve.ResolveMark;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class ExcelUtils {

    private static List<CellStyle> cellstyle_cache = null;

    public static void replaceExcel(String path, Workbook initWb, Sheet initSheet,Object data) throws Exception {
        File file = new File(path);
        FileInputStream fileInputStream = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);
        initSheet.setDefaultColumnWidth(25);//设置单元格宽度
        int sheetMergeCount = sheet.getNumMergedRegions();
        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstCell = ca.getFirstColumn();
            int lastCell = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            CellRangeAddress m_region = new CellRangeAddress(firstRow,lastRow,firstCell,lastCell);  //合并0列的n--n+2行
            initSheet.addMergedRegion(m_region);
        }

        if (Objects.isNull(sheet)) {
            return ;
        }
        cellstyle_cache = new ArrayList<CellStyle>();
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            Row initRow = initSheet.createRow(rowIndex);
            for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                Cell initCell = initRow.createCell(cellIndex);
                if(Objects.isNull(cell)){
                    continue ;
                }
                CellStyle style =cell.getCellStyle();
                CellStyle cellStyle = getCellStyle(style, initWb, workbook);
                initCell.setCellStyle(cellStyle);
                initCell.setAsActiveCell();
                initCell = ResolveMark.replaceMark(cell,initCell,data);
            }
        }
    }

    @SuppressWarnings("deprecation")
    public static String getCellvalue(Cell cell) {
        String value = "";
        if (Objects.isNull(cell)) {
            return null;
        }
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                value = String.valueOf((int) cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = String.valueOf(cell.getCellFormula());
                break;
            default:
                break;
        }
        return value;
    }

    @SuppressWarnings("deprecation")
    public static Cell putValue(Cell cell, int cellType, String value) {
        if (Objects.isNull(cell)) {
            return null;
        }
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                cell.setCellValue(Double.valueOf(value));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
                cell.setCellValue(value);
                break;
            case Cell.CELL_TYPE_FORMULA:
                cell.setCellType(Cell.CELL_TYPE_FORMULA);
                cell.setCellFormula(value);
                break;
            default:
                cell.setCellValue(value);
                break;
        }
        return null;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param rowIndex
     * @param cellIndex
     * @return
     */
    private String getMergedRegionValue(Sheet sheet ,int rowIndex , int cellIndex){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(rowIndex >= firstRow && rowIndex <= lastRow){

                if(cellIndex >= firstColumn && cellIndex <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);

                    return getCellvalue(fCell) ;
                }
            }
        }

        return null ;
    }

    /**
     * 解决cellStyle 只能创建4000个的问题
     * @param copyStyle
     * @return
     */
    @SuppressWarnings("deprecation")
    private static CellStyle getCellStyle(CellStyle copyStyle,Workbook wb,Workbook copyBook){
        if(Objects.isNull(cellstyle_cache) || cellstyle_cache.size() == 0){
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.cloneStyleFrom(copyStyle);
            cellstyle_cache.add(cellStyle);
            return cellStyle;
        }
        for(CellStyle style : cellstyle_cache){
            if(style.getAlignment() == copyStyle.getAlignment()
                    //边框和边框颜色
                    && style.getBorderBottom() == copyStyle.getBorderBottom()
                    && style.getBorderLeft() == copyStyle.getBorderLeft()
                    && style.getBorderRight() == copyStyle.getBorderRight()
                    && style.getBorderTop() == copyStyle.getBorderTop()
                    && style.getTopBorderColor() == copyStyle.getTopBorderColor()
                    && style.getBottomBorderColor() == copyStyle.getBottomBorderColor()
                    && style.getRightBorderColor() == copyStyle.getRightBorderColor()
                    && style.getLeftBorderColor() == copyStyle.getLeftBorderColor()
//                    背景和前景
                    && style.getFillBackgroundColor() == copyStyle.getFillBackgroundColor()
                    && style.getFillForegroundColor() == copyStyle.getFillForegroundColor()
                    && style.getHidden() == copyStyle.getHidden()
                    && style.getVerticalAlignment() == copyStyle.getVerticalAlignment()

                    && style.getWrapText() == copyStyle.getWrapText()
                    && style.getIndention() == copyStyle.getIndention()//首行缩进
                    && style.getLocked() == copyStyle.getLocked()
                    && style.getRotation() == copyStyle.getRotation()//旋转
                    && style.getDataFormat() == copyStyle.getDataFormat()
                    && style.getFillPattern() == copyStyle.getFillPattern()
                ){
                Font font = wb.getFontAt(style.getFontIndex());
                Font copyFont = copyBook.getFontAt(copyStyle.getFontIndex());
                if(font.getColor()==copyFont.getColor()
                        && font.getBold() == copyFont.getBold()
                        && font.getFontHeight() == copyFont.getFontHeight()
                        && font.getUnderline() == copyFont.getUnderline()
                        && font.getIndex() == copyFont.getIndex()
                        && font.getFontName() == copyFont.getFontName()
                        && font.getItalic() == copyFont.getItalic()){
                    return style;
                }
            }
        }
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.cloneStyleFrom(copyStyle);
        cellstyle_cache.add(cellStyle);
        return cellStyle;
    }
}
